N-of-N Iterations

This page contains the following sections:

• N-of-N Overview
• N-of-N Optimization
• N-of-N Usage Scenario

For detailed step-by-step instructions on using N-of-N wizard, see the N-of-N Tutorial.

N-of-N Overview

The N-of-N wizard allows you to perform a series of nested loop queries (iterations) across multiple hierarchies and to display the results in a grid. When you run the query, the series of nested loops are performed in succession, where each loop inherits the output data from the previous loop. Each loop is associated with a single row hierarchy and a single loop filter.

The N-of-N wizard generates visual results only and does NOT generate any custom elements. If you wish to save N-of-N results, you can create a separate report for each N-of-N generation and then save your book to ensure your results are preserved for future reference.

N-of-N Optimization

Check the Optimize box to improve improve N-of-N processing time by checking first for empty cells and removing them from the query using the NONEMPTY Function. For details, read about query options.

NOTE: When Optimize is checked, the filter outcome might render unexpected results depending on the content of the data model. Therefore, the Optimize box should be used with care and results should be observed.

N-of-N Usage Scenario

The following scenario demonstrates how to use the N-of-N wizard.

Step 1 - Set Up Hierarchies

Before opening the N-of-N wizard, you need to set up your desired columns and rows using the Metadata Tree. In order to run the N-of-N wizard, you are required to specify two or more row hierarchies. In this scenario,we have specified three row hierarchies, as shown below.

When setting up your row hierarchies, it is recommended to use the "All" option to achieve the broadest possible search. For example, for the Products hierarchy, you can select "All Products/Products" in the Elements Panel. This will cause a search through all products when a nested loop is performed across the hierarchy.

Step 2 - Choose a Column Basis

Upon opening the N-of-N wizard, you are required to select a single column around which to display grid results. In this scenario, three column options are available in the pop-up list (Customer, No Discount, Reseller) and we select the "Customer" column. The content of the pop-up list depends upon your column selections in the Metadata Tree.

Step 3 - Apply Row Filters

For each of the three row hierarchies, we click the filter icon and specify a data filter. The following data filters are available:

• Top Count
• Top Percent
• Top Sum
• Bottom Count
• Bottom Percent
• Bottom Sum
• Non Empty
• None

For example, for the Products hierarchy we specify the following filter:

• We select the Filter Type as "Top Count".
• We specify a count value of "4".
• We select "Sales" as the measure for which we filter this particular row hierarchy.

The table below shows the three filters that we specify for this scenario.

ROW	FILTER
Month Dates	None
Products	Top Count 4 (based on Sales)
Sales Teams	Top Count 2 (based on Sales)

Distinguish Between Measure Types

When using the N-of-N wizard, it's important to distinguish between the two measure types:

• Measures within N-of-N Wizard - A measure selection in the Advanced Filter dialog determines how the filter is performed for the particular row hierarchy.
• Measures in Metadata Tree - The measure selections in the Metadata Tree determine which headers appear in the grid. This is standard Office BI functionality.

Step 4 - View Grid Before N-of-N

Before we run the N-of-N wizard, the grid appears as follows.

Step 5 - View Grid After N-of-N

When we run the N-of-N wizard, the grid content is updated as follows:

• Month Dates - The first query loop arranges all items by year, without any filtering.

• Products - The second query loop finds the top four products (based on sales) for each year.
• Sales Team - The third query loop finds the top two sales persons (based on sales) for each of the top four products.
• The text "Iteratively Generated" appears in the report header, as shown below. This text is deleted from the report header if N-of-N functionality is removed from the report.